package mysql

import (
	"io/ioutil"
	"log"
	"os"
	"os/exec"
	"regexp"
	"strings"
)

var regexMysqlType string = "(bigint|binary|bit|blob|char|date|datetime|decimal|double|enum|float|tinyint|int|integer|linestring|longblob|longtext|text|tinytext|varchar|time|timestamp|year)"
var regexKeyName string = "(`|')?\\w+(`|')?"
var regexComment string = "COMMENT\\s+'[ \\S]*'"
var regexCreateEnd string = "\\)[ \\S]*;"

var suffix string = "Data"

func ProcessSql(sqlfile string, packageName string, genwork bool, workDir string, workPackage string) error {
	data, err := os.ReadFile(sqlfile)
	if err != nil {
		log.Printf("ReadConfigure error:%v\n", err)
		log.Panicln("Read file error:", err)
	}

	sd := string(data)
	// remove the comments
	reg0 := regexp.MustCompile(regexComment)
	strs := reg0.FindAllString(sd, -1)
	for _, s := range strs {
		sd = strings.ReplaceAll(sd, s, "")
	}
	// ioutil.WriteFile("noComment.log", []byte(sd), 0755)
	reg1 := regexp.MustCompile(regexCreateEnd)
	ends := reg1.FindAllString(sd, -1)
	for _, s := range ends {
		sd = strings.ReplaceAll(sd, s, ");")
	}

	// ioutil.WriteFile("noComment.log", []byte(sd), 0755)

	reg2 := regexp.MustCompile("(create|CREATE)\\s+(table|TABLE)\\s+(`|')?\\w+(`|')?")

	is := reg2.FindAllStringIndex(sd, -1)
	log.Println(is)
	for i := 0; i < len(is); i++ {
		in := is[i]
		log.Println(sd[in[0]:in[1]])
	}

	parseSql(sd, is, packageName, genwork, workDir, workPackage)
	return nil
}

func sqlTypeToGo(sqlType string) (string, bool) {
	var r string
	var u bool = false
	switch sqlType {
	case "bigint":
		r = "int64"
	case "binary", "bit", "blob", "char", "linestring", "longblob", "longtext", "text", "tinytext", "varchar":
		r = "string"
	case "date", "datetime", "time", "timestamp", "year", "decimal":
		r = "string"
		u = true
	case "double":
		r = "double"
	case "float":
		r = "float"
	case "enum", "tinyint", "int", "integer":
		r = "int"
	}

	return r, u
}

func firstToUpper(src string) string {
	if len(src) > 0 {
		result := strings.ToUpper(src[0:1]) + src[1:]
		return result
	}

	return ""
}

func underlineToUpper(src string) string {
	con := firstToUpper(src)
	index := strings.Index(con, "_")
	for index != -1 {
		if index == len(con)-1 {
			con = con[:index]
		} else {
			con = con[:index] + firstToUpper(con[index+1:])
		}
		index = strings.Index(con, "_")
	}

	return con
}

/*
	   func AddArea(d *ud.AreaData) (int64, error) {
	   	if d == nil {
	   		ud.ELogger.Println("param nil")
	   		return 0, errors.New("param nil")
	   	}

	   	r, err := db.Exec("INSERT INTO parking_area (name,parking_id,type) VALUES(?,?,?)", d.Name, d.ParkingId, d.Type)
	   	if err != nil {
	   		ud.ELogger.Println("AddArea error:", err)
	   		return 0, err
	   	}

	   	id, err := r.LastInsertId()
	   	if err != nil {
	   		ud.ELogger.Println("LastInsertId error:", err)
	   		return 0, err
	   	}
	   	ud.ILogger.Println("AddArea success")

	   	return id, nil
	   }

	   func UpdatePgParking(d PgParkingData, db *sql.DB) error {
		if db == nil {
			return errors.New("db unused")
		}

		_, err := db.Exec("UPDATE pg_parking SET name=?,type=?,cell_count=?,address=?,owner=?,owner_phone=?,deleted=? WHERE id=?", d.Name, d.Type, d.CellCount, d.Address, d.Owner, d.OwnerPhone, d.Deleted, d.Id)
		return err
	}

	func DeletePgParking(d PagParkingData, db *sql.DB) error {
		if db == nil {
			return errors.New("db unused")
		}

		_, err := db.Exec("DELETE FROM pg_parking WHERE id = 1")
		return err
	}


	func AddParking(d *ud.PgParkingData) (int64, error) {
		if d == nil {
			lg.ELogger.Println("param nil")
			return 0, errors.New("param nil")
		}

		id, err := ud.InsertPgParking(*d, db)
		if err != nil {
			return 0, err
		}

		return id, nil
	}
	func EditParking(d *ud.PgParkingData) error {
		if d == nil {
			lg.ELogger.Println("param nil")
			return errors.New("param nil")
		}

		err := ud.UpdatePgParking(*d, db)
		return err
	}
	func DeleteParking(d *ud.PgParkingData) error {
		if d == nil {
			lg.ELogger.Println("param nil")
			return errors.New("param nil")
		}

		err := ud.DeletePgParking(*d, db)
		return err
	}

	func SelectPgCellUseRecord(id int64) (*ud.PgCellUseRecordData, error){
		r, err := ud.SelectPgCellUseRecord(id, db)
		return r, err
	}
*/
// func genWorkContents(head string, body string) string {
// 	tabname := parseTableName(head)
// 	keys := parseKey(body)
// 	if len(tabname) <= 0 || keys == nil {
// 		return ""
// 	}
// 	fun := func(presifx string, tabname string) string {
// 		retStr := ""
// 		retErr := ""
// 		retLast := ""
// 		tmpStr := ""
// 		paramList := ""
// 		typeName := underlineToUpper(tabname) + suffix
// 		if presifx == "Inserts" {
// 			retStr = "(int64, error)"
// 			retErr = " 0,err "
// 			retLast = " id, nil "
// 			tmpStr = "id, err := ud."
// 			paramList = "(d ud." + typeName + ") "
// 		} else {
// 			retStr = " error "
// 			retErr = " err "
// 			retLast = " nil "
// 			tmpStr = " err := ud."
// 			paramList = "(d *ud." + typeName + ") "
// 		}

// 		con := "func " + presifx + underlineToUpper(tabname) + paramList + retStr + " {\r\n" +
// 			`if d == nil {
// 			lg.ELogger.Println("param nil")
// 			err := errors.New("param nil")
// 			return ` + retErr + "\r\n}\r\n" +
// 			tmpStr + presifx + underlineToUpper(tabname) + "(*d, db)\r\n" +
// 			`if err != nil {
// 			return ` + retErr + "\r\n}\r\n\r\nreturn" + retLast + "\r\n}\r\n"

// 		if presifx == "Select" {
// 			con = "func " + presifx + underlineToUpper(tabname) + "(id int64) (*ud." + typeName + ",error) {\r\n" +
// 				"r, err := ud." + presifx + underlineToUpper(tabname) + "(id,db)\r\n" +
// 				"return r,err\r\n}\r\n"
// 		}

// 		return con
// 	}

// }

// keys 是sql 里面解析出来的字段名称和类型，tabname 是sql中的表名，typeName是表名对应到go的类型名称
func genGoType(keys [][]string, tabname string, typeName string) string {
	con := "type " + typeName + " struct {\n"
	for _, s := range keys {
		tp, us := sqlTypeToGo(s[1])
		ky := s[0]
		var jsonContent string
		if us {
			jsonContent = " `json:\"" + ky + "," + tp + "\"`\n"
		} else {
			jsonContent = " `json:\"" + ky + "\"`\n"
		}
		con = con + underlineToUpper(ky) + " " + tp + jsonContent
	}
	con = con + "}\n"

	return con
}

func genInsert(keys [][]string, tabname string, typeName string) string {
	fcon := "func Insert" + underlineToUpper(tabname) + "(d " + typeName + ",db *sql.DB) (int64, error) {\n" +
		"if db == nil { return 0, errors.New(\"db unused\")}\n" +
		"r, err := db.Exec(\"INSERT INTO " + tabname + " ("
	fconValues := " VALUES("
	fconParams := ","

	for _, s := range keys {
		ky := s[0]
		if ky != "create_time" && ky != "time_version" && ky != "deleted" {
			fcon = fcon + ky + ","
			if ky == "id" {
				// if insert failed the major key still incrementing
				fconValues = fconValues + "(SELECT id FROM(SELECT ((SELECT MAX(id) id FROM " + tabname + ") + 1) as id) tb),"
			} else {
				fconValues = fconValues + "?,"
				fconParams = fconParams + "d." + underlineToUpper(ky) + ","
			}
		}
	}

	fcon = fcon[:len(fcon)-1] + ")"
	fconValues = fconValues[:len(fconValues)-1] + ");\""
	fconParams = fconParams[:len(fconParams)-1] + ")\n"
	fcon = fcon + fconValues + fconParams
	fcon = fcon + "if err != nil { return 0, err }\n"
	fcon = fcon + "id, err := r.LastInsertId()\n" +
		"if err != nil { return 0,err }\n return id, nil"
	fcon = fcon + "}\n"

	return fcon
}

/*
func InsertPgCellCombines(ds []PgCellCombineData, db *sql.DB) error {
	if db == nil {
		return errors.New("db unused")
	}

	conn, err := db.Begin()
	if err != nil {
		return err
	}

	for _, d := range ds {
		r, err := conn.Exec("INSERT INTO pg_cell_combine (id,cell_id,camera_id,camera_index) VALUES((SELECT id FROM(SELECT ((SELECT MAX(id) id FROM pg_cell_combine) + 1) as id) tb),?,?,?);", d.CellId, d.CameraId, d.CameraIndex)
		if err != nil {
			return err
		}
		id, err := r.LastInsertId()
		if err != nil {
			return err
		}

		fmt.Println("id=", id)
	}

	return nil
}
*/
func genInserts(keys [][]string, tabname string, typeName string) string {
	fcon := "func Inserts" + underlineToUpper(tabname) + "(ds []" + typeName + ",db *sql.DB) error {\n" +
		"if db == nil { return errors.New(\"db unused\")}\n" +
		"conn, err := db.Begin()\r\n" +
		"if err != nil {\r\n" +
		"return err\r\n}\r\n\r\n" +
		"for _, d := range ds {\r\n" +
		"r, err := conn.Exec(\"INSERT INTO " + tabname + "("

	fconValues := " VALUES("
	fconParams := ","

	for _, s := range keys {
		ky := s[0]
		if ky != "create_time" && ky != "time_version" && ky != "deleted" {
			fcon = fcon + ky + ","
			if ky == "id" {
				// if insert failed the major key still incrementing
				fconValues = fconValues + "(SELECT id FROM(SELECT ((SELECT MAX(id) id FROM " + tabname + ") + 1) as id) tb),"
			} else {
				fconValues = fconValues + "?,"
				fconParams = fconParams + "d." + underlineToUpper(ky) + ","
			}
		}
	}

	fcon = fcon[:len(fcon)-1] + ")"
	fconValues = fconValues[:len(fconValues)-1] + ");\""
	fconParams = fconParams[:len(fconParams)-1] + ")\r\n"
	fcon = fcon + fconValues + fconParams
	fcon = fcon + `if err != nil {
		conn.Rollback()
		return err
	}
	id, err := r.LastInsertId()
	if err != nil {
		conn.Rollback()
		return err
	}` + "\r\nfmt.Println(\"id=\", id)\r\n" +
		"}\r\n" +
		`	err = conn.Commit()
		if err != nil {
			return err
		}
		return nil`

	fcon = fcon + "}\r\n"

	return fcon
}

func genUpdate(keys [][]string, tabname string, typeName string) string {
	ucon := "func Update" + underlineToUpper(tabname) + "(d " + typeName + ",db *sql.DB) error {\r\n" +
		"if db == nil { return errors.New(\"db unused\")}\r\n" +
		"_, err := db.Exec(\"UPDATE " + tabname + " SET "

	uconParams := " WHERE id=?\","
	uconWhere := "d.Id)\r\n"

	for _, s := range keys {
		ky := s[0]
		if ky != "create_time" && ky != "time_version" && ky != "deleted" {
			if ky == "id" {
			} else {
				ucon = ucon + ky + "=?,"
				uconParams = uconParams + "d." + underlineToUpper(ky) + ","
			}
		}
	}
	ucon = ucon[:len(ucon)-1] + uconParams + uconWhere
	ucon = ucon + "return err\r\n" + "}\r\n"

	return ucon
}

func genDelete(keys [][]string, tabname string, typeName string) string {
	dcon := "func Delete" + underlineToUpper(tabname) + "(d " + typeName + ",db *sql.DB) error {\r\n" +
		"if db == nil { return errors.New(\"db unused\")}\r\n" +
		"_, err := db.Exec(\"DELETE FROM " + tabname + " WHERE id=?\","
	dconWhere := "d.Id)\r\n"

	dcon = dcon + dconWhere
	dcon = dcon + "return err\r\n" + "}\r\n"

	return dcon
}

func genSoftDelete(keys [][]string, tabname string, typeName string) string {
	sdcon := "func SoftDelete" + underlineToUpper(tabname) + "(d " + typeName + ",db *sql.DB) error {\r\n" +
		"if db == nil { return errors.New(\"db unused\")}\r\n" +
		"_, err := db.Exec(\"UPDATE " + tabname + " SET deleted=1 WHERE id=?\","
	sdconWhere := "d.Id)\r\n"
	sdcon = sdcon + sdconWhere
	sdcon = sdcon + "return err\r\n" + "}\r\n"

	return sdcon
}

func genSelect(keys [][]string, tabname string, typeName string) string {
	secon := "func Select" + underlineToUpper(tabname) + "(id int64,db*sql.DB) (*" + typeName + ",error) {\r\n" +
		"if id <= 0 { return nil,errors.New(\"id is error\")}\r\n" +
		"r, err := db.Query(\"SELECT * FROM " + tabname + " WHERE id=?\",id)\r\n" +
		"if err != nil { fmt.Println(\"query:\", err)\r\n return nil,errors.New(\"query error\") }\r\n" +
		`defer func() {
			if r != nil {
				r.Close()
			}
		}()` +
		"\r\nvar d " + typeName +
		"\r\nfor r.Next() {\r\n" +
		"err = r.Scan("
	seconParams := ""

	for _, s := range keys {
		ky := s[0]
		// if ky != "create_time" && ky != "time_version" && ky != "deleted" {
		seconParams = seconParams + "&d." + underlineToUpper(ky) + ","

	}

	secon = secon + seconParams[:len(seconParams)-1]
	secon = secon + ")\r\n"
	secon = secon + "if err != nil {\r\n" +
		"return nil, err}\r\n" +
		"if d.Id == id { break }\r\n}\r\n" +
		"return &d, err\r\n}\r\n"

	return secon
}

func generateGoContent(head string, body string) (string, string, string, string, string) {
	// var con, fcon, ucon string

	tabname := parseTableName(head)
	keys := parseKey(body)
	log.Println("tabname:", tabname)
	log.Println("keys:", keys)
	if len(tabname) <= 0 || keys == nil {
		return "", "", "", "", ""
	}

	typeName := underlineToUpper(tabname) + suffix

	fcon := genInsert(keys, tabname, typeName)
	ucon := genUpdate(keys, tabname, typeName)
	dcon := genDelete(keys, tabname, typeName)
	sdcon := genSoftDelete(keys, tabname, typeName)
	secon := genSelect(keys, tabname, typeName)

	dcon = dcon + sdcon + secon
	con := genGoType(keys, tabname, typeName)

	inserts := genInserts(keys, tabname, typeName)

	return con, fcon, ucon, dcon, inserts
}

func parseSql(sql string, index [][]int, packageName string, genwork bool, workDir string, workPackage string) error {
	packageFunc := func(pkgName string) string {
		headCon := "package " + pkgName + "\r\n"
		return headCon
	}

	headCon := packageFunc(packageName) + `import (
		"database/sql"
		"errors"
		"fmt"
		)` + "\r\n"
	dataCon := ""
	funcCon := "\n"

	workCon := "\r\n\r\n// place copy the under to database.go file\r\n"
	benchmark := ""

	inserts := "\r\n"

	source := sql

	var head string
	var before []int
	for i := 0; i < len(index); i++ {
		in := index[i]
		thead := source[in[0]:in[1]]

		if i != len(index)-1 && len(before) > 0 {
			c, fc, uc, dc, isc := generateGoContent(head, source[before[1]:in[0]])
			dataCon = dataCon + c
			funcCon = funcCon + fc + uc + dc
			inserts = inserts + isc
			if genwork {
				i, u, d := genWorkContent(head, source[before[1]:in[0]], packageName)
				workCon = workCon + i + u + d
				benchmark = benchmark + genBenchmarkContent(head, source[before[1]:in[0]], packageName)
			}
		} else if i == len(index)-1 {
			c, fc, uc, dc, isc := generateGoContent(head, source[before[1]:in[0]])
			dataCon = dataCon + c
			funcCon = funcCon + fc + uc + dc
			inserts = inserts + isc

			subStr := source[in[1]:]
			ind := strings.Index(subStr, ";")
			tc := subStr[:ind]
			log.Println("c:", tc)

			c, fc, uc, dc, isc = generateGoContent(thead, tc)
			dataCon = dataCon + c
			funcCon = funcCon + fc + uc + dc
			inserts = inserts + isc

			if genwork {
				i, u, d := genWorkContent(head, source[before[1]:in[0]], packageName)
				workCon = workCon + i + u + d
				benchmark = benchmark + genBenchmarkContent(head, source[before[1]:in[0]], packageName)

				i, u, d = genWorkContent(thead, tc, packageName)
				workCon = workCon + i + u + d
				benchmark = benchmark + genBenchmarkContent(thead, tc, packageName)
			}
		}

		head = thead
		before = in
	}

	// log.Println("content:", dataCon)
	filename := packageName + ".go"

	err := ioutil.WriteFile(filename, []byte(headCon+dataCon+funcCon), 0755)
	if err != nil {
		log.Println("write file error:", err)
		return err
	}

	insertsName := packageName + "Inserts.go"
	err = ioutil.WriteFile(insertsName, []byte(headCon+inserts), 0755)
	if err != nil {
		log.Println("write file error:", err)
		return err
	}

	fmtFunc := func(filename string) {
		cmdStr := "gofmt -l -w " + filename
		cmd := exec.Command("/bin/sh", "-c", cmdStr)
		if _, err := cmd.Output(); err != nil {
			log.Panicln(err)
		}
	}

	if genwork {
		workName := workDir + workPackage + "AutoGen.go"
		head := packageFunc(workPackage) +
			"import(\r\n\"errors\"\r\n)\r\n"

		err = ioutil.WriteFile(workName, []byte(head+workCon), 0755)
		if err != nil {
			log.Println("write file error:", err)
			return err
		}
		fmtFunc(workName)

		benchmarkName := workDir + "benchmark_test.go"
		err = ioutil.WriteFile(benchmarkName, []byte(head+benchmark), 0755)
		if err != nil {
			log.Println("write file error:", err)
			return err
		}
		fmtFunc(benchmarkName)
	}
	fmtFunc(filename)
	fmtFunc(insertsName)

	return nil
}

func parseTableName(head string) string {
	reg := regexp.MustCompile(`(create|CREATE)\s+(table|TABLE)\s+`)
	st := reg.FindAllString(head, -1)
	if len(st) == 1 {
		s := strings.Replace(head, st[0], "", -1)
		s = strings.ReplaceAll(s, "`", "")
		s = strings.ReplaceAll(s, "'", "")

		return s
	}

	return ""
}

func parseKey(body string) [][]string {
	var result [][]string

	values := strings.Split(body, ",")

	for i := 0; i < len(values); i++ {
		sourceStr := values[i]
		lowerStr := strings.ToLower(sourceStr)

		regStr := regexKeyName + "\\s+" + regexMysqlType

		reg := regexp.MustCompile(regStr)
		v := reg.FindAllString(sourceStr, -1)
		var regResult string
		if v == nil {
			ins := reg.FindAllStringIndex(lowerStr, -1)
			if ins != nil {
				regResult = sourceStr[ins[0][0]:ins[0][1]]
			} else {
				return result
			}
		} else {
			regResult = v[0]
		}

		reg1 := regexp.MustCompile(regexKeyName)
		keyNames := reg1.FindAllString(regResult, -1)
		if keyNames == nil {
			log.Panicln("key find error!")
		}

		reg2 := regexp.MustCompile(regexMysqlType)
		regResult = strings.ToLower(regResult)
		types := reg2.FindAllString(regResult, -1)
		if types == nil {
			log.Panicln("type find error!")
		}

		var vs []string
		key := keyNames[0]
		tp := types[0]
		key = strings.ReplaceAll(key, "`", "")
		key = strings.ReplaceAll(key, "'", "")

		vs = append(vs, key, tp)
		result = append(result, vs)

		log.Println("regResult:", regResult)
	}

	return result
}
